﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text.RegularExpressions;
using Model;
using BLL;

public partial class PersonInfo_PersonImport : BasePage
{
    PersonInfoBO pbo = new PersonInfoBO();
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    /// <summary>
    /// QE
    /// </summary>
    public class qe
    {
        public string name { get; set; }
        public int num { get; set; }
    }

    protected void btnUpload_Click(object sender, EventArgs e)
    {
        string filePath = string.Empty;
        try
        {
            filePath = UploadFile.FileName;
            string name = filePath.Substring(filePath.LastIndexOf("\\") + 1).ToString();
            string exp = name.Substring(name.LastIndexOf(".") + 1);

            if (exp.ToLower() != "xls" && exp.ToLower() != "xlsx" && exp.ToLower() != "csv")
            {
                ClientScript.RegisterStartupScript(this.GetType(), "key1", "<script>alert('请上传(.xls|.xlsx|.csv)格式文件！');</script>");
            }
            else
            {
                string FileNam_Time = "";
                string FName = DateTime.Now.ToString("yyyyMMddHHmmss") + name;
                FileNam_Time = FName;//日期和名称组合
                string path = HttpContext.Current.Request.MapPath("FileUpload/ImportExcel/");
                // string url = "../FileUpload/QYRD/" + CurrentUser.Name + "/new" + FileNam_Time;
                DirectoryInfo upDir = new DirectoryInfo(path);
                if (!upDir.Exists)
                {
                    upDir.Create();
                }
                filePath = path + FileNam_Time;
                UploadFile.SaveAs(filePath);
                ImportExcel(filePath);

            }
        }
        catch (Exception ex)
        {
            AlertMessage("Err：" + ex.Message.ToString());
            return;
        }
        finally
        {

        }

    }

    private void ImportExcel(string excelFile)
    {
        DataSet ds = new DataSet();
        OleDbConnection myConn = new OleDbConnection();
        try
        {
            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
            if (rbt2007.Checked)
                strCon = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile + @";Extended Properties=""Excel 12.0;HDR=YES;""";
            myConn = new OleDbConnection(strCon);
            myConn.Open();
            System.Data.DataTable schemaTable = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });     //获取表名
            DataTable dt = new DataTable();
            if (schemaTable.Rows.Count > 0)
            {
                string tablename = "Sheet1" + "$";//获到表名 
                string sql = "select * from [" + tablename + "]";  //从工作表查询。
                OleDbDataAdapter da = new OleDbDataAdapter(sql, myConn);
                da.Fill(ds);


                dt.Columns.Add("rownum");  //rownum
                dt.Columns.Add("name1");   //BDNT Part No.          BDNT Part Name
                dt.Columns.Add("name2");   //BDNT Part Name         BDNT Part No.
                dt.Columns.Add("name3");   //Supplier Code          BDNT Revision No.
                dt.Columns.Add("name4");   //BDNT Revision No.      BDNT Revision Status
                dt.Columns.Add("name5");   //BDNT Revision Status   Supplier Code 
                dt.Columns.Add("name6");   //Color                  Planed PPAR date
                dt.Columns.Add("name7");   //Planed PPAR date       Color   
                dt.Columns.Add("name8");   //QE                     QE
                dt.Columns.Add("name9");   //Supplier Admin         Supplier Admin
                dt.Columns.Add("name10");  //结果                   结果
                dt.Columns.Add("name11");
                dt.Columns.Add("name12");
                dt.Columns.Add("name13");
                dt.Columns.Add("name14");
                dt.Columns.Add("name15");
                int num = 0;
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    num++;
                    DataRow r = dt.NewRow();
                    r[0] = num;                         //rownum
                    r[1] = dr[0]; //rownum              //part name
                    r[2] = dr[1];//part no              //part no
                    r[3] = dr[2];//part name            //revision no
                    r[4] = dr[3];//supplier name        //revision status
                    r[5] = dr[4];//status               //supplier code
                    r[6] = dr[5]; //color               //ppar date
                    r[7] = dr[6]; //date                //color
                    r[8] = dr[7]; //qe                  //qe
                    r[9] = dr[8];//Supplier Admin       //admin
                    r[10] = dr[9];
                    r[11] = dr[10];
                    r[12] = dr[11];
                    r[13] = dr[12];
                    r[14] = dr[13];

                    if (dr[0].ToString() == "" && dr[1].ToString() == "" && dr[2].ToString() == "" && dr[3].ToString() == "" && dr[4].ToString() == "")
                        continue;

                    string bopp = Checkpp(dr[0].ToString());
                    r[15] = bopp; //结果
                    dt.Rows.Add(r);
                }
                GridView1.DataSource = dt;
                GridView1.DataBind();
                if (ds.Tables[0].Rows.Count > 0)
                    btnSave.Visible = true;
                else
                    btnSave.Visible = false;
            }

            Session["dsexcel"] = dt;
            btnSave.Visible = true;

        }
        catch (Exception ex)
        {
            AlertMessage("Err:" + ex.Message.ToString());
            GC.Collect();
        }
        finally
        {
            myConn.Close();
            myConn.Dispose();
        }
    }

    public bool CheckEmail(string Email)
    {
        if (!getRegexInput(Email, @"\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*"))
        {
            return false;
        }
        else
        {
            return true;
        }

    }

    public bool getRegexInput(string str, string regexStr)//str是要被验证的字符串，regexStr为正则表达式
    {
        Regex rStr = new Regex(regexStr);
        bool re = false;
        if (rStr.IsMatch(str))
        {
            re = true;
        }
        return re;
    }

    /// <summary>
    /// 验证SupplierCode
    /// </summary>
    /// <param name="SupplierCode"></param>
    /// <returns></returns>
    private string Check(string SupplierCode)
    {

        return "0";
        //return "<font color='red'>Supplier Name Does not exist</font>";
    }

    /// <summary>
    /// 验证QE，part，suppliercode,supplieradmin
    /// </summary>
    /// <param name="PartNo"></param>
    /// <param name="supplierid"></param>
    /// <param name="color"></param>
    /// <param name="qe"></param>
    /// <param name="supplieradmin"></param>
    /// <returns></returns>
    private string Checkpp(string PerNo)
    {
        List<PersonInfo> list = pbo.GetModelList(" PersonNo='" + PerNo + "'");
        if (list.Count > 0)
        {
            return "<font color='red'>编号存在</font>";
        }
        return "<font color='blue'>验证成功</font>";
    }

    /// <summary>
    /// 验证PartNo合法性
    /// </summary>
    /// <param name="PartNo"></param>
    /// <returns></returns>
    private bool CheckPartNo(string PartNo)
    {
        //if (PartNo.Length < 11)
        //    return false;
        //if (PartNo.Substring(0, 4).ToUpper() != "TIG-")
        //    return false;
        //else return true;
        return true;
    }

    /// <summary>
    /// 保存
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnSave_Click(object sender, EventArgs e)
    {
        //string message = "";
        //int cnum = 0;
        //try
        //{

        //    if (Session["dsexcel"] != null)
        //    {
        //        DataTable ds = Session["dsexcel"] as DataTable;
        //        PersonInfo p = null;
        //        foreach (DataRow dr in ds.Rows)
        //        {
        //            if (dr["name15"].ToString().Contains("成功"))
        //            {
                        
        //                List<PersonInfo> list = pbo.GetModelList(" PersonNo='" + dr[1].ToString() + "'");
        //                if (list.Count > 0)
        //                {
        //                    message += dr[1].ToString().Trim() + " 编号重复 \n";
        //                }
        //                else
        //                {
        //                    p = new PersonInfo {
        //                    PersonNo=dr[1].ToString(),
        //                    Name=dr[2].ToString(),
        //                    Phone=dr[3].ToString(),
        //                    JT=dr[4].ToString(),
        //                    Store=dr[5].ToString(),
        //                    ZW=dr[6].ToString(),
        //                    Superior=dr[7].ToString(),
        //                    HYSJ=dr[8].ToString(),
        //                    SHSheng=dr[9].ToString(),
        //                    SHShi=dr[10].ToString(),
        //                    SHDZ=dr[11].ToString(),
        //                    SHR=dr[12].ToString(),
        //                    SHRLXDH=dr[13].ToString(),
        //                    Post=dr[14].ToString(),
        //                    HYZT=0,
        //                    CreateDate=DateTime.Now,
        //                    Dream=0,
        //                    DreamGift=0,
        //                    FromUserName="",
        //                    GQJF=0,
        //                    JLJF=0,
        //                    LHRQ=DateTime.Parse("2099-12-31"),
        //                    Photo="",
        //                    RHRQ=DateTime.Now,
        //                    Sex=true,
        //                    SumIntegral=0,
        //                    SurplusIntegral=0
        //                    };
        //                  int num=  pbo.Add(p);
        //                  if (num > 0)
        //                      cnum++;
        //                }
        //            }
        //        }
        //    }
        //    btnSave.Visible = false;
        //    lblMessage.Text = message;
        //    AlertMessage("已导入"+cnum+"条数据");

        //}
        //catch (Exception ex)
        //{
        //    AlertMessage(ex.Message.ToString());
        //}
        //finally
        //{
        //    Session["dsexcel"] = null;
        //}
    }

    private string GetQLevelNum(int num)
    {
        string value = "";
        switch (num.ToString().Length)
        {
            case 1:
                value += "00" + num;
                break;
            case 2:
                value += "0" + num;
                break;
            case 3:
                value += num.ToString();
                break;
            default:
                value += "001";
                break;
        }
        return value;
    }
}
